Grokking SQL for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

1% completed

Vote For New Content
Introduction
Course Overview
What is RDBMS?
What is SQL?
A Quick Review of SQL
Read data from table
WHERE Clause
Operators
Handle NULLs in SQL
DISTINCT Clause
ORDER BY Clause
LIMIT and OFFSET
GROUP BY
HAVING
Aggregate Functions
Order of Execution
Order of SQL Query Execution
SQL Functions
Number Functions
String Functions
Date Functions
Nested Queries
Nested Query
Types of Subqueries
JOINS
Introduction
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
SELF JOIN
SET Operations
What are SET operations?
UNION
INTERSECT
EXCEPT
A Quick Review of DDL
Keys in SQL
CREATE
ALTER
DROP
TRUNCATE
Indexes
A Quick Review of DML
INSERT
UPDATE
DELETE
Database Backup & Restore
Backup Database
Restore Database
Interview Problems - Easy
1. Major Cities
2. Not boring movies
3. Triangle Judgement
4. Biggest Single Number
5. Product Sales Analysis I
6. Product Sales Analysis II
7. Game Play Analysis I
8. Game Play Analysis II
9. Queries Quality and Percentage
10. Average Selling Price
11. Weather Type in Each Country
12. Students and Examinations
13. Ads Performance
14. Group Sold Products By The Date
15. Employee Email Verification
16. Sellers With No Sales
17. Average Time of Process
18. Find Followers Count
19. Ad-Free Sessions
20. Bikes Last Time Used
21. Daily User Engagement Levels
22. Employee Attendance Record
23. Library Book Loans
24. Customer Purchase Summary
25. Long Comments (Easy)
26. Unused Accounts
27. Patient Appointment No-Shows
28. Books and Authors
29. High School Attendance
30. Employee Absences
31. Green Product Identification (Easy)
32. Employee Department Exclusion (Easy)
33. Sales Person (Easy)
34. Employee Task Tracking (Easy)
Interview Problems - Medium
1. Winning Candidate
2. Second Degree Follower
3. 2nd Highest Salary
4. Game Play Analysis III
5. Game Play Analysis IV
6. Active Businesses
7. Product Price on a Specific Date
8. Monthly Transactions II
9. Team Scores in Football Tournament
10. Page Recommendations
11. Running Total for Different Genders
12. Evaluate Boolean Expression
13. Active Users
14. Find Interview Candidates
15. Suspicious Bank Accounts
16. Order Fulfillment Rate (Medium)
17. Last Person to Fit in the Elevator (Medium)
18. Retail Expansion Analysis (Medium)
19. Employee Collaboration Networks (Medium)
20. Second Fastest Time in Races (Medium)
Interview Problems - Hard
1. Trips and Users
2. Human Traffic of Stadium
3. Students Report By Geography
4. Game Play Analysis V
5. Number of Transactions per Visit
6. Find the Quiet Students in All Exams
7. First and Last Call On the Same Day
8. Build the Equation
9. Longest Winning Streak
10. Dynamic Pivoting of a Table
11. Dynamic Unpivoting of a Table
12. Generate the Invoice
13. Popularity Percentage
14. Viewers Turned Streamers
15. Status of Flight Tickets
1. High Performing Employees (Easy)
2. Student Course Averages (Easy)
3. Library Book Checkout Percentage (Easy)
4. Employee Performance and Improvement Rate (Easy)
5. Find Popular Posts (Easy)
6. Main Office Assignment for Each Employee (Easy)
7. Rectangle Validation (Easy)
8. Library Book Checkouts by Date (Easy)
9. Same-Day Appointment Fulfillment (Medium)
10. Departments with High Earning Employees (Medium)
11. Students Who Attended All Courses (Medium)
12. Library Book Checkouts First Year (Medium)
13. Repeated Values in Sequence (Medium)
14. School Top Achievers by Subject (Hard)
6. Find the Quiet Students in All Exams

Problem

Table: Student

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id is the primary key (column with unique values) for this table.
student_name is the name of the student.

Table: Exam

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| exam_id       | int     |
| student_id    | int     |
| score         | int     |
+---------------+---------+
(exam_id, student_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that the student with student_id had a score points in the exam with id exam_id.

Problem Definition

A quiet student is the one who took at least one exam and did not score the highest or the lowest score.

Write a solution to report the students (student_id, student_name) being quiet in all exams. Do not return the student who has never taken any exam.

Return the result table ordered by student_id.

Example

Image

Output

Image

Try It Yourself

MYSQL

. . . .

Student =

Exam =

Solution

To identify quiet students—those who have taken at least one exam and never scored the highest or the lowest in any of their exams—we can follow a structured approach using SQL's window functions and subqueries. This involves ranking students within each exam, filtering out those who achieved top or bottom ranks, and finally selecting the remaining students who meet the quiet student criteria.

Approach Overview

  1. Rank Students' Scores Within Each Exam:
    • Assign ranks to students based on their scores in each exam to identify the highest and lowest scorers.
  2. Identify Students with Highest or Lowest Scores:
    • Extract students who have ever ranked first (highest score) or last (lowest score) in any exam.
  3. Determine Quiet Students:
    • Exclude students identified in the previous step from the list of all students who have taken at least one exam. The remaining students are the quiet students.
  4. Retrieve Quiet Students' Details:
    • Fetch the student_id and student_name of the quiet students from the Student table.
  5. Finalize the Results:
    • Select distinct candidate details and present them in the desired format, ordered by student_id.

SQL Query

WITH cte AS ( SELECT exam_id, student_id, RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score, RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score FROM Exam ) SELECT DISTINCT e.student_id, s.student_name FROM Exam e LEFT JOIN Student s ON s.student_id = e.student_id WHERE e.student_id NOT IN ( SELECT student_id FROM cte WHERE high_score = 1 OR low_score = 1 ) ORDER BY e.student_id;

Step-by-Step Approach

Step 1: Rank Students' Scores Within Each Exam (cte)

Assign ranks to students within each exam to determine who scored the highest and the lowest.

SQL Query:

WITH cte AS ( SELECT exam_id, student_id, RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score, RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score FROM Exam ) SELECT * FROM cte;

Explanation:

  • RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score:
    • Assigns a rank to each student in descending order of their scores within each exam_id. The student(s) with the highest score receive a rank of 1.
  • RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score:
    • Assigns a rank to each student in ascending order of their scores within each exam_id. The student(s) with the lowest score receive a rank of 1.

Intermediate Output After Step 1 (cte):

+---------+------------+-----------+----------+ | exam_id | student_id | high_score| low_score| +---------+------------+-----------+----------+ | 10 | 1 | 3 | 1 | | 10 | 2 | 2 | 2 | | 10 | 3 | 1 | 3 | | 20 | 1 | 1 | 1 | | 30 | 1 | 3 | 1 | | 30 | 3 | 2 | 2 | | 30 | 4 | 1 | 3 | | 40 | 1 | 3 | 1 | | 40 | 2 | 2 | 2 | | 40 | 4 | 1 | 3 | +---------+------------+-----------+----------+

Step 2: Identify Students with Highest or Lowest Scores

Find all students who have ever ranked first (highest score) or last (lowest score) in any exam.

SQL Query:

WITH cte AS ( SELECT exam_id, student_id, RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score, RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score FROM Exam ) SELECT DISTINCT student_id FROM cte WHERE high_score = 1 OR low_score = 1;

Explanation:

  • WHERE high_score = 1 OR low_score = 1:
    • Filters the cte to include only those students who have either the highest (high_score = 1) or the lowest (low_score = 1) score in any exam.

Intermediate Output After Step 2:

+------------+ | student_id | +------------+ | 1 | | 3 | | 4 | +------------+

Step 3: Determine Quiet Students

Exclude students identified in Step 2 from those who have taken at least one exam. The remaining students are the quiet students.

SQL Query:

WITH cte AS ( SELECT exam_id, student_id, RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score, RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score FROM Exam ), excluded AS ( SELECT DISTINCT student_id FROM cte WHERE high_score = 1 OR low_score = 1 ) SELECT DISTINCT e.student_id, s.student_name FROM Exam e JOIN Student s ON s.student_id = e.student_id WHERE e.student_id NOT IN (SELECT student_id FROM excluded) ORDER BY e.student_id;

Explanation:

  • JOIN Student s ON s.student_id = e.student_id:
    • Associates each exam record with the corresponding student's details.
  • WHERE e.student_id NOT IN (SELECT student_id FROM excluded):
    • Filters out students who have ever scored the highest or the lowest in any exam.
  • SELECT DISTINCT e.student_id, s.student_name:
    • Ensures that each quiet student appears only once in the final output, even if they have taken multiple exams.

Intermediate Output After Step 3:

+------------+--------------+ | student_id | student_name | +------------+--------------+ | 2 | Jade | +------------+--------------+

Explanation of Output:

  • Student 2 (Jade):
    • Exam 10: Scored 80 (middle score)
    • Exam 40: Scored 70 (middle score)
    • Never scored the highest or the lowest in any exam.

Note:

  • Students 1, 3, and 4 are excluded as they have scored the highest or lowest in at least one exam.
5. Number of Transactions per Visit
7. First and Last Call On the Same Day
Mark as Completed

On this page

Problem

Problem Definition

Example

Output

Try It Yourself

Solution

Approach Overview

SQL Query

Step-by-Step Approach